package com.venta.autopartes.dao;

import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.venta.autopartes.branch.Branch;
import com.venta.autopartes.branch.BranchDepartment;
import com.venta.autopartes.branch.BranchImage;
import com.venta.autopartes.branch.BranchUserRole;
import com.venta.autopartes.car.Currency;
import com.venta.autopartes.user.Phone;
import com.venta.autopartes.user.PhoneContact;
import com.venta.autopartes.user.User;

public class BranchDAO extends BaseDAO{
	private static final String SELECT_BRANCH_MESSAGE="SELECT venau_branch_info_welcome_message AS MESSAGE,venau_branch_info_host AS HOSTNAME FROM `SGDLS_T_branch_info` AS BRANCH_INFO WHERE BRANCH_INFO.venau_branch_info_host=?";
	private static final String SELECT_BRANCH_BY_HOSTNAME="SELECT venau_branch_id AS ID, venau_responsible_user_id_FK AS RESPONSABLE_ID,venau_matrix_branch_id_FK AS MATRIX_BRANCH_ID,	venau_branch_name AS NAME,	venau_branch_email AS EMAIL, venau_branch_twitter as TWITTER, venau_branch_youtube as YOUTUBE, venau_branch_facebook as FACEBOOK, venau_branch_map AS MAP,venau_branch_info_welcome_message AS MESSAGE,	venau_branch_info_host AS HOSTNAME FROM `SGDLS_T_branch` AS BRANCH, `SGDLS_T_branch_info` AS BRANCH_INFO WHERE BRANCH.venau_branch_id=BRANCH_INFO.venau_branch_id_FK AND BRANCH_INFO.venau_branch_info_host=?";
	private static final String SELECT_CONTACT_PHONE_BY_BRANCH_ID="SELECT venau_branch_phoneNumber_id AS BRANCH_PHONE_ID, venau_phone_catalog_id_FK AS PHONE_ID, venau_branch_phoneNumber_phone AS PHONE_NUMBER ,venau_phone_catalog_type_name AS PHONE_TYPE FROM `SGDLS_T_branch_phoneNumber` as PHONE_NUMBER, `SGDLS_T_phone_catalog`  AS PHONE_CATALOG WHERE PHONE_CATALOG.venau_phone_catalog_id=PHONE_NUMBER.venau_phone_catalog_id_FK AND PHONE_NUMBER.venau_branch_id_FK = ? ";
	private static final String SELECT_BRANCHES_BY_MATRIX_BRANCH_ID="SELECT venau_branch_id AS ID, venau_responsible_user_id_FK AS RESPONSABLE_ID,venau_matrix_branch_id_FK AS MATRIX_BRANCH_ID,venau_branch_name AS NAME,	venau_branch_email AS EMAIL, venau_branch_twitter as TWITTER, venau_branch_youtube as YOUTUBE, venau_branch_facebook as FACEBOOK, venau_branch_map AS MAP,venau_branch_info_welcome_message AS MESSAGE,	venau_branch_info_host AS HOSTNAME FROM `SGDLS_T_branch` AS BRANCH, `SGDLS_T_branch_info` AS BRANCH_INFO WHERE BRANCH.venau_branch_id=BRANCH_INFO.venau_branch_id_FK AND BRANCH.venau_matrix_branch_id_FK=?";
	private static final String SELECT_ALL_BRANCHES="SELECT venau_branch_id AS ID, venau_responsible_user_id_FK AS RESPONSABLE_ID,venau_matrix_branch_id_FK AS MATRIX_BRANCH_ID,venau_branch_name AS NAME,	venau_branch_email AS EMAIL, venau_branch_twitter as TWITTER, venau_branch_youtube as YOUTUBE, venau_branch_facebook as FACEBOOK, venau_branch_map AS MAP,venau_branch_info_welcome_message AS MESSAGE,	venau_branch_info_host AS HOSTNAME FROM `SGDLS_T_branch` AS BRANCH, `SGDLS_T_branch_info` AS BRANCH_INFO WHERE BRANCH.venau_branch_id=BRANCH_INFO.venau_branch_id_FK";
	private static final String SELECT_BRANCHES_MATRIXES="SELECT venau_branch_id AS ID, venau_responsible_user_id_FK AS RESPONSABLE_ID,venau_matrix_branch_id_FK AS MATRIX_BRANCH_ID,venau_branch_name AS NAME,	venau_branch_email AS EMAIL, venau_branch_twitter as TWITTER, venau_branch_youtube as YOUTUBE, venau_branch_facebook as FACEBOOK, venau_branch_map AS MAP,venau_branch_info_welcome_message AS MESSAGE,	venau_branch_info_host AS HOSTNAME FROM `SGDLS_T_branch` AS BRANCH, `SGDLS_T_branch_info` AS BRANCH_INFO WHERE BRANCH.venau_branch_id=BRANCH_INFO.venau_branch_id_FK AND BRANCH.venau_matrix_branch_id_FK IS NULL";
	private static final String SELECT_SINGLE_BRANCHES="SELECT venau_branch_id AS ID, venau_responsible_user_id_FK AS RESPONSABLE_ID,venau_matrix_branch_id_FK AS MATRIX_BRANCH_ID,venau_branch_name AS NAME,	venau_branch_email AS EMAIL, venau_branch_twitter as TWITTER, venau_branch_youtube as YOUTUBE, venau_branch_facebook as FACEBOOK, venau_branch_map AS MAP,venau_branch_info_welcome_message AS MESSAGE,	venau_branch_info_host AS HOSTNAME FROM `SGDLS_T_branch` AS BRANCH, `SGDLS_T_branch_info` AS BRANCH_INFO WHERE BRANCH.venau_branch_id=BRANCH_INFO.venau_branch_id_FK AND BRANCH.venau_matrix_branch_id_FK IS NOT NULL";
	private static final String SELECT_BRANCH_BY_BRANCH_ID="SELECT venau_branch_id AS ID, venau_responsible_user_id_FK AS RESPONSABLE_ID,venau_matrix_branch_id_FK AS MATRIX_BRANCH_ID,	venau_branch_name AS NAME,	venau_branch_email AS EMAIL, venau_branch_twitter as TWITTER, venau_branch_youtube as YOUTUBE, venau_branch_facebook as FACEBOOK, venau_branch_map AS MAP,venau_branch_info_welcome_message AS MESSAGE,	venau_branch_info_host AS HOSTNAME FROM `SGDLS_T_branch` AS BRANCH, `SGDLS_T_branch_info` AS BRANCH_INFO WHERE BRANCH.venau_branch_id=BRANCH_INFO.venau_branch_id_FK AND BRANCH.venau_branch_id=?";
	private static final String SELECT_DEPARTMENTS="SELECT venau_branch_departments_catalog_id AS ID, venau_branch_departments_catalog_name AS NAME FROM SGDLS_T_branch_departments_catalog ORDER BY venau_branch_departments_catalog_name";
	private static final String SELECT_BRANCH_USER_ROLES="SELECT venau_branch_user_roles_catalog_id AS ID, venau_branch_user_roles_catalog_name AS NAME FROM SGDLS_T_branch_user_roles_catalog ORDER BY venau_branch_user_roles_catalog_name";
	private static final String SELECT_BRANCH_ID_BY_RESPONSIBLE="SELECT venau_branch_id AS BRANCH_ID FROM SGDLS_T_branch  WHERE venau_responsible_user_id_FK=?";
	
	private static final String INSERT_NEW_BRANCH="INSERT INTO SGDLS_T_branch (venau_responsible_user_id_FK,venau_matrix_branch_id_FK,venau_branch_name,venau_branch_email,	venau_branch_twitter,venau_branch_youtube,venau_branch_facebook,venau_branch_map) VALUES(?,?,?,?,?,?,?,?);";
	private static final String INSERT_NEW_BRANCH_INFO="INSERT INTO SGDLS_T_branch_info (venau_branch_id_FK, venau_branch_info_welcome_message, venau_branch_info_host) VALUES(?,?,?);";
	private static final String INSERT_NEW_BRANCH_PHONES_NUMBER="INSERT INTO SGDLS_T_branch_phoneNumber  (venau_branch_id_FK,venau_phone_catalog_id_FK,venau_branch_phoneNumber_phone) VALUES (?,?,?)";
	private static final String INSERT_NEW_BRANCH_ADDRESS="INSERT INTO SGDLS_T_branches_address (venau_branch_id_FK,venau_suburb_catalog_id_FK,venau_address_street_name,venau_address_interior_number,venau_address_exterior_number) VALUES (?,?,?,?,?)";
	private static final String INSERT_NEW_BRANCH_IMAGES="INSERT INTO SGDLS_T_branch_images   (venau_branch_id_FK, venau_branch_images_url_image) VALUES (?,?)";
	
	private static final String UPDATE_BRANCH="UPDATE SGDLS_T_branch SET venau_responsible_user_id_FK=?, venau_matrix_branch_id_FK=?,venau_branch_name=? , venau_branch_email=?, venau_branch_twitter=?, venau_branch_youtube=?, venau_branch_facebook=?, venau_branch_map=? WHERE venau_branch_id=?";
	private static final String UPDATE_BRANCH_INFO="UPDATE SGDLS_T_branch_info SET venau_branch_info_welcome_message=?, venau_branch_info_host=? WHERE venau_branch_id_FK=?";
	private static final String UPDATE_BRANCH_PHONES_NUMBER="UPDATE SGDLS_T_branch_phoneNumber SET venau_phone_catalog_id_FK=?, venau_branch_phoneNumber_phone=? WHERE venau_branch_id_FK=? AND 	venau_branch_phoneNumber_id=?";
	private static final String UPDATE_BRANCH_ADDRESS="UPDATE SGDLS_T_branches_address SET venau_suburb_catalog_id_FK=?, venau_address_street_name=?,venau_address_interior_number=? , venau_address_exterior_number=? WHERE venau_branch_id_FK=?";
	private static final String UPDATE_BRANCH_IMAGES="UPDATE SGDLS_T_branch_images SET venau_branch_images_url_image=? WHERE venau_branch_id_FK=?";
	
	private static final String DELETE_BRANCH_BY_BRANCH_ID_BRANCH="DELETE FROM SGDLS_T_branch  WHERE venau_branch_id=? AND venau_branch_name=?";
	
	public static String getBranchMessage(String hostName){
		String branchMessage=null;
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try{
			preparedStatement = connection.prepareStatement(SELECT_BRANCH_MESSAGE);
			preparedStatement.setString(1, hostName);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				branchMessage=resultSet.getString("MESSAGE");
				System.out.println("Displayed Branch Message : " + branchMessage);
			}
			
		}catch(SQLException sqlException){
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		return branchMessage;
	}
	
	public static Branch getBranchByBranchId(int branchId){
		Branch branch=null;
		int userResponsibleId=0;
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try{
			preparedStatement = connection.prepareStatement(SELECT_BRANCH_BY_BRANCH_ID);
			preparedStatement.setInt(1, branchId);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				branch=new Branch();
				branch.setBranchId(resultSet.getInt("ID"));
				branch.setEmail(resultSet.getString("EMAIL"));
				branch.setTwitter(resultSet.getString("TWITTER"));
				branch.setYoutube(resultSet.getString("YOUTUBE"));
				branch.setFacebook(resultSet.getString("FACEBOOK"));
				branch.setMap(resultSet.getString("MAP"));
				branch.setHostname(resultSet.getString("HOSTNAME"));
				if(resultSet.getString("MATRIX_BRANCH_ID")!=null){
					
					Branch matrixBranch=new Branch();
					matrixBranch.setBranchId(resultSet.getInt("MATRIX_BRANCH_ID"));
					branch.setMatrix(matrixBranch);
					System.out.println("There is a matrix for this branch " + matrixBranch.getBranchId());
					
				}
				branch.setName(resultSet.getString("NAME"));
				if(resultSet.getString("RESPONSABLE_ID")!=null){
					userResponsibleId=resultSet.getInt("RESPONSABLE_ID");
				}
				branch.setWelcomeMessage(resultSet.getString("MESSAGE"));
				
				System.out.println("method:getBranchByBranchId --> Branch: "+ branch.getName()+" Displayed Branch Message : " + branch.getWelcomeMessage());
			}
			
		}catch(SQLException sqlException){
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		branch.setPhoneContact(getPhoneContactById(branch.getBranchId()));
		branch.setResponsibleUser(UserDAO.getUserById(userResponsibleId));
		branch.setAddress(LocationDAO.getBranchAddressById(branch.getBranchId()));
		branch.setBranches(getBranchesByMatrixBranchId(branch.getBranchId()));
		return branch;
	}
	
	public static Branch getBranchByHostName(String hostName){
		Branch branch=null;
		int userResponsibleId=0;
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try{
			preparedStatement = connection.prepareStatement(SELECT_BRANCH_BY_HOSTNAME);
			preparedStatement.setString(1, hostName);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				branch=new Branch();
				branch.setBranchId(resultSet.getInt("ID"));
				branch.setEmail(resultSet.getString("EMAIL"));
				branch.setTwitter(resultSet.getString("TWITTER"));
				branch.setYoutube(resultSet.getString("YOUTUBE"));
				branch.setFacebook(resultSet.getString("FACEBOOK"));
				branch.setMap(resultSet.getString("MAP"));
				branch.setHostname(resultSet.getString("HOSTNAME"));
				if(resultSet.getString("MATRIX_BRANCH_ID")!=null){
					Branch matrixBranch=new Branch();
					matrixBranch.setBranchId(resultSet.getInt("MATRIX_BRANCH_ID"));
					branch.setMatrix(matrixBranch);
					System.out.println("There is a matrix for this branch " + matrixBranch.getBranchId());
					
				}
				branch.setName(resultSet.getString("NAME"));
				if(resultSet.getString("RESPONSABLE_ID")!=null){
					userResponsibleId=resultSet.getInt("RESPONSABLE_ID");
				}
				branch.setWelcomeMessage(resultSet.getString("MESSAGE"));
				
				System.out.println("method:getBranchByHostName --> Branch: "+ branch.getName()+" Displayed Branch Message : " + branch.getWelcomeMessage());
			}
			
		}catch(SQLException sqlException){
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		branch.setPhoneContact(getPhoneContactById(branch.getBranchId()));
		branch.setResponsibleUser(UserDAO.getUserById(userResponsibleId));
		branch.setAddress(LocationDAO.getBranchAddressById(branch.getBranchId()));
		branch.setBranches(getBranchesByMatrixBranchId(branch.getBranchId()));
		return branch;
	}
	
	public static List<Branch> getBranchesByMatrixBranchId(int branchId){
		List<Branch> branchesList= new ArrayList<Branch>();
		
		System.out.println("method:getBranchesByMatrixBranchId--> branchId: "+ branchId);
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try{
			preparedStatement = connection.prepareStatement(SELECT_BRANCHES_BY_MATRIX_BRANCH_ID);
			preparedStatement.setInt(1, branchId);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				Branch branch=new Branch();
				branch.setBranchId(resultSet.getInt("ID"));
				branch.setEmail(resultSet.getString("EMAIL"));
				branch.setTwitter(resultSet.getString("TWITTER"));
				branch.setYoutube(resultSet.getString("YOUTUBE"));
				branch.setFacebook(resultSet.getString("FACEBOOK"));
				branch.setMap(resultSet.getString("MAP"));
				branch.setHostname(resultSet.getString("HOSTNAME"));
				if(resultSet.getString("MATRIX_BRANCH_ID")!=null){
					Branch matrixBranch=new Branch();
					matrixBranch.setBranchId(resultSet.getInt("MATRIX_BRANCH_ID"));
					branch.setMatrix(matrixBranch);
					
				}
				branch.setName(resultSet.getString("NAME"));
				if(resultSet.getString("RESPONSABLE_ID")!=null){
					User userResponsible=new User();
					userResponsible.setUserId(resultSet.getInt("RESPONSABLE_ID"));
					branch.setResponsibleUser(userResponsible);
				}
				branch.setWelcomeMessage(resultSet.getString("MESSAGE"));
				branchesList.add(branch);
				
				System.out.println("method:getBranchesByMatrixBranchId --> Matrix Branch: "+ branch.getName()+" Displayed Branch Message : " + branch.getWelcomeMessage());
			}
			
		}catch(SQLException sqlException){
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		for(Branch branch:branchesList){
			branch.setPhoneContact(getPhoneContactById(branch.getBranchId()));
			UserDAO userDao=new UserDAO();
			branch.setResponsibleUser(userDao.getUserById(branch.getResponsibleUser().getUserId()));
			branch.setAddress(LocationDAO.getBranchAddressById(branch.getBranchId()));
			//branch.setBranches(getBranchesByMatrixBranchId(branch.getBranchId()));
		}
		
		return branchesList;
	}
	
	public static List<Branch> getBranchesMatrixes(){
		List<Branch> branchesList= new ArrayList<Branch>();
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try{
			preparedStatement = connection.prepareStatement(SELECT_BRANCHES_MATRIXES);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				Branch branch=new Branch();
				branch.setBranchId(resultSet.getInt("ID"));
				branch.setEmail(resultSet.getString("EMAIL"));
				branch.setTwitter(resultSet.getString("TWITTER"));
				branch.setYoutube(resultSet.getString("YOUTUBE"));
				branch.setFacebook(resultSet.getString("FACEBOOK"));
				branch.setMap(resultSet.getString("MAP"));
				branch.setHostname(resultSet.getString("HOSTNAME"));
				if(resultSet.getString("MATRIX_BRANCH_ID")!=null){
					Branch matrixBranch=new Branch();
					matrixBranch.setBranchId(resultSet.getInt("MATRIX_BRANCH_ID"));
					branch.setMatrix(matrixBranch);
					
				}
				branch.setName(resultSet.getString("NAME"));
				if(resultSet.getString("RESPONSABLE_ID")!=null){
					User userResponsible=new User();
					userResponsible.setUserId(resultSet.getInt("RESPONSABLE_ID"));
					branch.setResponsibleUser(userResponsible);
				}
				branch.setWelcomeMessage(resultSet.getString("MESSAGE"));
				branchesList.add(branch);
				
				System.out.println("getBranchesMatrixes --> Matrix Branch: "+ branch.getName()+" Displayed Branch Message : " + branch.getWelcomeMessage());
			}
			
		}catch(SQLException sqlException){
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		for(Branch branch:branchesList){
			branch.setPhoneContact(getPhoneContactById(branch.getBranchId()));
			UserDAO userDao=new UserDAO();
			branch.setResponsibleUser(userDao.getUserById(branch.getResponsibleUser().getUserId()));
			branch.setAddress(LocationDAO.getBranchAddressById(branch.getBranchId()));
			//branch.setBranches(getBranchesByMatrixBranchId(branch.getBranchId()));
		}
		
		return branchesList;
	}
	
	public static List<Branch> getSingleBranches(){
		List<Branch> branchesList= new ArrayList<Branch>();
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try{
			preparedStatement = connection.prepareStatement(SELECT_SINGLE_BRANCHES);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				Branch branch=new Branch();
				branch.setBranchId(resultSet.getInt("ID"));
				branch.setEmail(resultSet.getString("EMAIL"));
				branch.setTwitter(resultSet.getString("TWITTER"));
				branch.setYoutube(resultSet.getString("YOUTUBE"));
				branch.setFacebook(resultSet.getString("FACEBOOK"));
				branch.setMap(resultSet.getString("MAP"));
				branch.setHostname(resultSet.getString("HOSTNAME"));
				if(resultSet.getString("MATRIX_BRANCH_ID")!=null){
					Branch matrixBranch=new Branch();
					matrixBranch.setBranchId(resultSet.getInt("MATRIX_BRANCH_ID"));
					branch.setMatrix(matrixBranch);
					
				}
				branch.setName(resultSet.getString("NAME"));
				if(resultSet.getString("RESPONSABLE_ID")!=null){
					User userResponsible=new User();
					userResponsible.setUserId(resultSet.getInt("RESPONSABLE_ID"));
					branch.setResponsibleUser(userResponsible);
				}
				branch.setWelcomeMessage(resultSet.getString("MESSAGE"));
				branchesList.add(branch);
				
				System.out.println("getSingleBranches --> Matrix Branch: "+ branch.getName()+" Displayed Branch Message : " + branch.getWelcomeMessage());
			}
			
		}catch(SQLException sqlException){
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		for(Branch branch:branchesList){
			branch.setPhoneContact(getPhoneContactById(branch.getBranchId()));
			UserDAO userDao=new UserDAO();
			branch.setResponsibleUser(userDao.getUserById(branch.getResponsibleUser().getUserId()));
			branch.setAddress(LocationDAO.getBranchAddressById(branch.getBranchId()));
			//branch.setBranches(getBranchesByMatrixBranchId(branch.getBranchId()));
		}
		
		return branchesList;
	}
	
	public static List<Branch> getAllBranches(){
		List<Branch> branchesList= new ArrayList<Branch>();
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try{
			preparedStatement = connection.prepareStatement(SELECT_ALL_BRANCHES);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				Branch branch=new Branch();
				branch.setBranchId(resultSet.getInt("ID"));
				branch.setEmail(resultSet.getString("EMAIL"));
				branch.setTwitter(resultSet.getString("TWITTER"));
				branch.setYoutube(resultSet.getString("YOUTUBE"));
				branch.setFacebook(resultSet.getString("FACEBOOK"));
				branch.setMap(resultSet.getString("MAP"));
				branch.setHostname(resultSet.getString("HOSTNAME"));
				if(resultSet.getString("MATRIX_BRANCH_ID")!=null){
					Branch matrixBranch=new Branch();
					matrixBranch.setBranchId(resultSet.getInt("MATRIX_BRANCH_ID"));
					branch.setMatrix(matrixBranch);
					
				}
				branch.setName(resultSet.getString("NAME"));
				if(resultSet.getString("RESPONSABLE_ID")!=null){
					User userResponsible=new User();
					userResponsible.setUserId(resultSet.getInt("RESPONSABLE_ID"));
					branch.setResponsibleUser(userResponsible);
				}
				branch.setWelcomeMessage(resultSet.getString("MESSAGE"));
				branchesList.add(branch);
				
				System.out.println("getAllBranches --> Matrix Branch: "+ branch.getName()+" Displayed Branch Message : " + branch.getWelcomeMessage());
			}
			
		}catch(SQLException sqlException){
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		for(Branch branch:branchesList){
			branch.setPhoneContact(getPhoneContactById(branch.getBranchId()));
			UserDAO userDao=new UserDAO();
			branch.setResponsibleUser(userDao.getUserById(branch.getResponsibleUser().getUserId()));
			branch.setAddress(LocationDAO.getBranchAddressById(branch.getBranchId()));
			//branch.setBranches(getBranchesByMatrixBranchId(branch.getBranchId()));
		}
		
		return branchesList;
	}
	
	
	
	public static PhoneContact getPhoneContactById(int branchId){
		PhoneContact phoneContact = null;
		List<Phone> phoneList=new ArrayList<Phone>();
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try{
			preparedStatement = connection.prepareStatement(SELECT_CONTACT_PHONE_BY_BRANCH_ID);
			preparedStatement.setInt(1, branchId);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				
				Phone phone=new Phone(resultSet.getInt("PHONE_ID"),resultSet.getString("PHONE_TYPE"),resultSet.getString("PHONE_NUMBER"));
				phone.setUserPhoneId(resultSet.getInt("BRANCH_PHONE_ID"));
				phoneList.add(phone);
				
				System.out.println(phone.getUserPhoneId()+" - "+phone.getPhoneType()+" : " + phone.getPhoneNumber());
			}
			phoneContact=new PhoneContact();
			phoneContact.setPhoneList(phoneList);
			
		}catch(SQLException sqlException){
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		return phoneContact;
	}

	public static List<BranchDepartment> getBranchDepartmentsList() {
		List<BranchDepartment> branchDepartmentList = new ArrayList<BranchDepartment>();

		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_DEPARTMENTS);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				BranchDepartment branchDepartment = new BranchDepartment(resultSet.getInt("ID"),	resultSet.getString("NAME"));
				branchDepartmentList.add(branchDepartment);
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		} finally {
			closeConnection();
		}

		return branchDepartmentList;
	}
	
	public static List<BranchUserRole> getBranchUserRoleList() {
		List<BranchUserRole> branchUserRoleList = new ArrayList<BranchUserRole>();

		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_BRANCH_USER_ROLES);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				BranchUserRole branchUserRole = new BranchUserRole(resultSet.getInt("ID"),	resultSet.getString("NAME"));
				branchUserRoleList.add(branchUserRole);
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)BrachDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)BrachDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		} finally {
			closeConnection();
		}

		return branchUserRoleList;
	}
	
	public static boolean insertNewBranch(Branch branch){
		
		connection = com.venta.autopartes.utils.Connection.getConnection();

		boolean result=false;
		try {

			preparedStatement = connection.prepareStatement(INSERT_NEW_BRANCH);
			preparedStatement.setInt(1,branch.getResponsibleUser().getUserId());
			 
			if(branch.getMatrix()!=null && branch.getMatrix().getBranchId() >0){
				preparedStatement.setInt(2, branch.getMatrix().getBranchId());
			}else{
				preparedStatement.setString(2,null);
			}
			preparedStatement.setString(3,branch.getName());
			preparedStatement.setString(4,branch.getEmail());
			preparedStatement.setString(5,branch.getTwitter());
			preparedStatement.setString(6,branch.getYoutube());
			preparedStatement.setString(7,branch.getFacebook());
			preparedStatement.setString(8,branch.getMap());

			int val = preparedStatement.executeUpdate();
			preparedStatement.clearParameters();
			
			preparedStatement=connection.prepareStatement(SELECT_BRANCH_ID_BY_RESPONSIBLE);
			preparedStatement.setInt(1,branch.getResponsibleUser().getUserId());
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				branch.setBranchId(resultSet.getInt("BRANCH_ID"));
			}
			
			System.out.println("Inserted BranchId --> " + branch.getBranchId());
			
			preparedStatement.clearParameters();
			
			if (val == 0) {
				System.out.println("(Class)BranchDAO - (ErrorType)The branch was not inserted");
			}else{
				preparedStatement = connection.prepareStatement(INSERT_NEW_BRANCH_INFO);
				preparedStatement.setInt(1,branch.getBranchId());
				preparedStatement.setString(2,branch.getWelcomeMessage());
				preparedStatement.setString(3, branch.getHostname());
				
				
				val = preparedStatement.executeUpdate();
				preparedStatement.clearParameters();
				if(val==0){
					System.out.println("(Class)BranchDAO - (ErrorType)The branch info was not inserted");
				}else{
					preparedStatement = connection.prepareStatement(INSERT_NEW_BRANCH_PHONES_NUMBER);
					for(Phone phone:branch.getPhoneContact().getPhoneList()){
						if(phone.getPhoneNumber()!=null && !phone.getPhoneNumber().equalsIgnoreCase("")){
							
								System.out.println("UserPhoneId : " + phone.getUserPhoneId());
								System.out.println("Phone TypeId : " + phone.getTypeId());
								System.out.println("Phone Type : " + phone.getPhoneType());
								System.out.println("Phone number : " + phone.getPhoneNumber());
							preparedStatement.setInt(1,branch.getBranchId());
							preparedStatement.setInt(2,phone.getTypeId());
							preparedStatement.setString(3,phone.getPhoneNumber());
							preparedStatement.addBatch();
						}
					}
					preparedStatement.executeBatch();
					preparedStatement.clearBatch();
					
					if(val==0){
						System.out.println("(Class)BranchDAO - (ErrorType)The phones branch were not inserted");
					}else{
						preparedStatement = connection.prepareStatement(INSERT_NEW_BRANCH_ADDRESS);
						preparedStatement.setInt(1,branch.getBranchId());
						preparedStatement.setInt(2,branch.getAddress().getSuburb().getSuburbId());
						preparedStatement.setString(3,branch.getAddress().getStreet());
						preparedStatement.setString(4,branch.getAddress().getInteriorNumber());
						preparedStatement.setString(5,branch.getAddress().getExteriorNumber());
						
						val = preparedStatement.executeUpdate();
						preparedStatement.clearParameters();
						if(val==0){
							System.out.println("(Class)BranchDAO - (ErrorType)The address was not inserted");
						}else{
							result=true;
							if(val==0){
								System.out.println("(Class)BranchDAO - (ErrorType)The address branch was not inserted");
							}else{
								preparedStatement = connection.prepareStatement(INSERT_NEW_BRANCH_IMAGES);

								for(BranchImage branchImage:branch.getBranchImageList() ){
									preparedStatement.setInt(1,branch.getBranchId());
									preparedStatement.setString(2,branchImage.getImageURL());
									preparedStatement.addBatch();
								}
								preparedStatement.executeBatch();
								preparedStatement.clearBatch();
								
								if(val==0){
									System.out.println("(Class)BranchDAO - (ErrorType)The images branch were not inserted");
								}
							}
						}
					}
					
					
				}
				
			}
			closeConnection();

		} catch (SQLException sqlException) {
			System.out.println("(Class)BranchDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BranchDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		return result;
	}
	
	
	public static boolean deleteBranchByBranch(Branch branch) {
		boolean result=true;
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {

			preparedStatement = connection.prepareStatement(DELETE_BRANCH_BY_BRANCH_ID_BRANCH);
			 
			preparedStatement.setInt(1, branch.getBranchId());
			preparedStatement.setString(2,branch.getName());
			

			int val = preparedStatement.executeUpdate();
			preparedStatement.clearParameters();
			
			System.out.println("Deleting BranchId --> " + branch.getBranchId());
			
			preparedStatement.clearParameters();
			
			if (val == 0) {
				System.out.println("(Class)BranchDAO - (ErrorType)The branch was not deleted");
			}
			closeConnection();

		} catch (SQLException sqlException) {
			System.out.println("(Class)BranchDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BranchDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		return result;
	}
	
	public static boolean updateBranchByBranch(Branch branch) {
		boolean result=true;
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try{

			preparedStatement = connection.prepareStatement(UPDATE_BRANCH);
			preparedStatement.setInt(1,branch.getResponsibleUser().getUserId());
			 
			if(branch.getMatrix()!=null && branch.getMatrix().getBranchId() >0){
				preparedStatement.setInt(2, branch.getMatrix().getBranchId());
			}else{
				preparedStatement.setString(2,null);
			}
			preparedStatement.setString(3,branch.getName());
			preparedStatement.setString(4,branch.getEmail());
			preparedStatement.setString(5,branch.getTwitter());
			preparedStatement.setString(6,branch.getYoutube());
			preparedStatement.setString(7,branch.getFacebook());
			preparedStatement.setString(8,branch.getMap());
			preparedStatement.setInt(9,branch.getBranchId());

			int val = preparedStatement.executeUpdate();
			preparedStatement.clearParameters();
			System.out.println("Updating branchId --> " + branch.getBranchId());
			
			if (val == 0) {
				System.out.println("(Class)BranchDAO - (ErrorType)The branch was not updated");
			}else{
				preparedStatement=connection.prepareStatement(UPDATE_BRANCH_INFO);
				preparedStatement.setString(1,branch.getWelcomeMessage());
				preparedStatement.setString(2,branch.getHostname());
				preparedStatement.setInt(3,branch.getBranchId());
				
				val = preparedStatement.executeUpdate();
				preparedStatement.clearParameters();
				
				if (val == 0) {
					System.out.println("(Class)BranchDAO - (ErrorType)The branch info was not updated");
				}else{
					preparedStatement = connection.prepareStatement(UPDATE_BRANCH_INFO);
					
					preparedStatement.setString(1,branch.getWelcomeMessage());
					preparedStatement.setString(2, branch.getHostname());
					preparedStatement.setInt(3,branch.getBranchId());
					
					
					val = preparedStatement.executeUpdate();
					preparedStatement.clearParameters();
					if(val==0){
						System.out.println("(Class)BranchDAO - (ErrorType)The branch info was not updated");
					}else{   
						preparedStatement = connection.prepareStatement(UPDATE_BRANCH_PHONES_NUMBER);
						for(Phone phone:branch.getPhoneContact().getPhoneList()){
							if(phone.getPhoneNumber()!=null && !phone.getPhoneNumber().equalsIgnoreCase("")){
								System.out.println("BranchPhoneId : " + phone.getUserPhoneId());
								System.out.println("Phone TypeId : " + phone.getTypeId());
								System.out.println("Phone Type : " + phone.getPhoneType());
								System.out.println("Phone number : " + phone.getPhoneNumber());
								
								preparedStatement.setInt(1,phone.getTypeId());
								preparedStatement.setString(2,phone.getPhoneNumber());
								preparedStatement.setInt(3,branch.getBranchId());
								preparedStatement.setInt(4, phone.getUserPhoneId());
								
								preparedStatement.addBatch();
							}
						}
						preparedStatement.executeBatch();
						preparedStatement.clearBatch();
						
						if(val==0){
							System.out.println("(Class)BranchDAO - (ErrorType)The phones branch were not updated");
						}else{
							preparedStatement = connection.prepareStatement(UPDATE_BRANCH_ADDRESS);
							preparedStatement.setInt(1,branch.getAddress().getSuburb().getSuburbId());
							preparedStatement.setString(2,branch.getAddress().getStreet());
							preparedStatement.setString(3,branch.getAddress().getInteriorNumber());
							preparedStatement.setString(4,branch.getAddress().getExteriorNumber());
							preparedStatement.setInt(5,branch.getBranchId());
							
							val = preparedStatement.executeUpdate();
							preparedStatement.clearParameters();
							if(val==0){
								System.out.println("(Class)BranchDAO - (ErrorType)The address was not updated");
							}else{
								result=true;
								if(val==0){
									System.out.println("(Class)BranchDAO - (ErrorType)The address branch was not updated");
								}else{
									preparedStatement = connection.prepareStatement(UPDATE_BRANCH_IMAGES);
	
									for(BranchImage branchImage:branch.getBranchImageList() ){
										
										preparedStatement.setString(1,branchImage.getImageURL());
										preparedStatement.setInt(2,branch.getBranchId());
										preparedStatement.addBatch();
									}
									preparedStatement.executeBatch();
									preparedStatement.clearBatch();
									
									if(val==0){
										System.out.println("(Class)BranchDAO - (ErrorType)The images branch were not updated");
									}
								}
							}
						}
					}
				}
			}
			closeConnection();

		} catch (SQLException sqlException) {
			System.out.println("(Class)BranchDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			result=false;
			try{
				connection.rollback();
			}catch(Exception exception){
				System.out.println("(Class)BranchDAO - (ErrorType)exception");
				exception.printStackTrace();
				result=false;
			}
		}finally{
			closeConnection();
		}
		
		return result;
	}

	
}


